#! /usr/bin/perl
#######################################################################################################
# Program: Barracuda/ESG Log report per domain
# Author: mm(at)elabnet.de
# Last Modified: 2006-08-30
#
# With this script you can ...
my $path = "/var/log/syslog.0";
use MIME::WordDecoder;
use DBI;
use Date::Parse;

# database information
$db="stats_syslog";
$host="localhost";
$port="10019";
$userid="";
$passwd="";
$connectionInfo="DBI:mysql:database=$db;$host:$port";

######################################
# Action Library - NOT USED *RESERVED*
######################################
#$action{0} = "Allowed Message";
#$action{1} = "Aborted Message";
#$action{2} = "Blocked Message";
#$action{3} = "Quarantined Message";
#$action{4} = "Tagged Message";
#$action{5} = "Deferred Message";
#$action{6} = "Per-User Quarantined Message";
#$action{7} = "Whitelisted Message";
$action{0} = "Allowed";
$action{1} = "Aborted";
$action{2} = "Blocked";
$action{3} = "Quarantined";
$action{4} = "Tagged";
$action{5} = "Deferred";
$action{6} = "Per-User Quarantine";
$action{7} = "Whitelisted";
$send_action{1} = "Delivered";
$send_action{2} = "Rejected";
$send_action{3} = "Deferred";
$send_action{4} = "Expired";

#############################
# Reason Library - NOT USED *RESERVED*
#############################
$reason{1} = "Virus";
$reason{2} = "Banned Attachment";
$reason{3} = "Blacklist";
$reason{4} = "Rate Control";
$reason{5} = "Too Many Messages In Session";
$reason{6} = "Timeout Exceeded";
$reason{7} = "No Such Domain";
$reason{8} = "Invalid Recipient";
$reason{9} = "Subject Filter Match";
$reason{11} = "Client IP";
$reason{12} = "Recipient Address Rejected";
$reason{13} = "No Valid Recipients";
$reason{14} = "Domain Not Found";
$reason{15} = "Sender Address Rejected";
$reason{17} = "Need Fully Qualified Recipient";
$reason{18} = "Need Fully Qualified Sender";
$reason{19} = "Unsupported Command";
$reason{20} = "MAIL FROM Syntax Error";
$reason{21} = "Bad Address Syntax";
$reason{22} = "RCPT TO Syntax Error";
$reason{23} = "Send EHLO/HELO First";
$reason{24} = "Need MAIL Command";
$reason{25} = "Nested MAIL Command";
$reason{27} = "EHLO/HELO Syntax Error";
$reason{30} = "Mail Protocol Error";
$reason{31} = "Score";
$reason{34} = "Header Filter Match";
$reason{35} = "Sender Block/Accept";
$reason{36} = "Recipient Block/Accept";
$reason{37} = "Body Filter Match";
$reason{38} = "Message Size Bypass";
$reason{39} = "Intention Analysis";
$reason{40} = "SPF/Caller ID";
$reason{41} = "Client Host Rejected";
$reason{44} = "Authentication Not Enabled";
$reason{45} = "Allowed Message Size Exceeded";
$reason{46} = "Too Many Recipients";
$reason{47} = "Need RCPT TO Command";
$reason{48} = "DATA Syntax Error";
$reason{49} = "Internal Error";
$reason{50} = "Too Many Hops";
$reason{55} = "Invalid Parameter Syntax";
$reason{56} = "STARTTLS Syntax Error";
$reason{57} = "TLS Already Active";
$reason{58} = "Too Many Errors";
$reason{59} = "Need STARTTLS First";
$reason{60} = "SPAM Fingerprint";
$reason{61} = "Barracuda Whitelist";
$reason{62} = "Barracuda Blocklist";


#############################
# MAIN
#############################


# make connection to database
$dbh = DBI->connect($connectionInfo,$userid,$passwd,{RaiseError => 0, PrintError => 0}) || die "Database connection not made: $DBI::errstr";

# collect newest record from DB
$query = "SELECT Timestamp FROM maillog ORDER BY Timestamp DESC LIMIT 0,1";
$sth = $dbh->prepare($query);
$sth->execute();
my( $lastdbentry );
$sth->bind_columns( \$lastdbentry );
$sth->fetch();
$sth->finish();

open(SYSLOG, "$path") || die "File not found!\n";
my $msgcount;
while (<SYSLOG>){
   my $line = $_;
   parse_log_line($line);
   }

# Modify the spamhaus entries based on what we've seen so far.
$query = "UPDATE maillog SET ReasonExtOrig=ReasonExt, ReasonExt='sbl-xbl.spamhaus.org' WHERE timestamp>DATE_ADD(NOW(),INTERVAL -25 HOUR) AND ReasonExt like '%sbl-xbl.spamhaus.org%' AND ReasonExtOrig=''";
$sth = $dbh->prepare($query);
$sth->execute();
$query = "UPDATE maillog SET ReasonExtOrig=ReasonExt, ReasonExt='www.spamhaus.org' WHERE timestamp>DATE_ADD(NOW(),INTERVAL -25 HOUR) AND ReasonExt like '%www.spamhaus.org%' AND ReasonExtOrig=''";
$sth = $dbh->prepare($query);
$sth->execute();

# disconnect from database
$dbh->disconnect;

print "Got to the end!\n";

#############################
# END
#############################


sub parse_log_line
{
# Grab the line we were given and create a new message hash for our message
my($line) = @_;
my %message = ();
# These are the components we may have parsed out of the message based on the service
my ($cluster, $ip, $id, $start_time, $end_time, $name, $info, $domain);
my ($enc, $sender, $recip, $score, $action, $reason, $reason_extra, $msgsize, $subject);
# Grab the main components from the line (IP, MSG_ID, START_TIME, END_TIME, SERVICE, INFO)
#
#
# NOTE: If this is for the SEND log line then the IP, as well as the START/END times are
# bogus values of 127.0.0.1 and 0/0 respectively
if( $line =~ /\S+..\S+ \S+ (\S+) \S+\]:\s+([^\s]+) ([^\s]+) (\d+) (\d+) (RECV|SCAN|SEND) (.*)$/)
   {
   # Grab the main pieces of the log entry and the process specific info
   ($cluster, $ip, $id, $start_time, $end_time, $name, $info) = ($1, $2, $3, $4, $5, $6, $7);
   # Set the connecting IP, message-id, start-time, and end-time if this wasn't
   # for the SEND service
   if( $name !~ /SEND/ )
      {
      $message{client} = $ip;
      $message{id} = $id;
      $message{start_time} = $start_time;
      $message{end_time} = $end_time;
      # Check if old entry already in db..
      if ((str2time($lastdbentry)-120) > $start_time) {
         return( $end_time );
         }
      }

   # Break out the process specific pieces from the info portion
   if( $name =~ /RECV/ )
      {
      # Break the MTA info up into sender/recip/action/reason/reason_extra
      if(substr($info,0,1) eq " ")
      {
        $info = "-" . $info;
      }
      if( $info =~ /([^\s]+)\s([^\s]+)\s(\d+)\s(\d+)\s(.*)$/ )
         {
         ($sender, $recip, $action, $reason, $reason_extra) = ($1, $2, $3, $4, $5);
         # Store the readable time of this message based on when it was started by
         # converting the unix time to its components and then sprintf’ing into readable form
         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($start_time);
         $message{time} = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $year+1900, $mon+1, $mday, $hour, $min, $sec);
         # Store the sender if we had one
         if( $sender ne '-' )
         {
            $message{from} = lc($sender);
         }
          else
         {
            $message{from} = "-";
         }
         # Store the recipient if we had one
         if( $recip ne '-' )
         {
            $recip = lc($recip);
            $message{mailto} = $recip;
            ($message{recp_user},$message{recp_domain}) = split(/@/,$recip);
         }
         # Set our action/reason codes
         $message{action_id} = $action;
         $message{reason_id} = $reason;
         # Pull in the reason_extra field. This should never be anything other
         # than ASCII since the mta doesn't have any multi-byte functionality
         # ... thus we don't need to eval it.
         if( $reason_extra ne '-' )
         {
            $message{reason_extra} = $reason_extra;
         }
         }
         else
         {
         print "Wrong RECV Format!\n";
         }

         # Write to database
         $message{action_text} = $action{$message{action_id}};
         if ($message{action_id} >0) {
            $message{reason_text} = $reason{$message{reason_id}};
            } else {
            $message{reason_text} = "(Allowed)";
            }
         if ($message{from} & $message{mailto}) {
            $query = "INSERT INTO maillog (Timestamp,Sender,Recipient,Subject,Action,Reason,ReasonExt,Score,SourceIP,MsgID,Domain,Size,Cluster) VALUES ('$message{time}','$message{from}','$message{mailto}','$message{subject}','$message{action_text}','$message{reason_text}','$message{reason_extra}','$message{spam_score}','$message{client}','$message{id}','$message{recp_domain}','$message{msgsize}','$cluster')";
            $sth = $dbh->prepare($query);
            $sth->execute();
         }
      }
   elsif( $name =~ /SCAN/ )
      {
      # Break the scanner info up into encrypted/sender/recip/score/action/reason/reason_extra/size/subject
      if( $info =~ /([^\s]+)\s([^\s]+)\s([^\s]+)\s([-\.\d+]+)\s(\d+)\s(\d+)\s(.*)\sSZ:(.*)\sSUBJ:(.*)$/ )
         {
         ($enc, $sender, $recip, $score, $action, $reason, $reason_extra, $msgsize, $subject) =
         ($1, $2, $3, $4, $5, $6, $7, $8, $9);
         # Store the readable time of this message based on when it was started by
         # converting the unix time to its components and then sprintf’ing into readable form
         my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($start_time);
         $message{time} = sprintf("%04d-%02d-%02d %02d:%02d:%02d", $year+1900, $mon+1, $mday, $hour, $min, $sec);
         # Store the sender if we had one
         if( $sender ne '-' )
            {
            $message{from} = $sender;
            }
            else
            {
               $message{from} = "-";
            }
         # Store the recipient if we had one and build the msg_file path
         if( $recip ne '-' )
            {
            $recip = lc($recip);
            $message{mailto} = $recip;
            ($message{recp_user},$message{recp_domain}) = split(/@/,$recip);
            }
         # Set the subject line, we need to decode the mime-crap
         if( $subject )
            {
            eval
               {
               # Note: if this is encoded you may want to decode it here and that
               # is why this section is in an eval – since nothing guarantees the
               # sender encoded the subject properly.
               $message{subject} = substr(unmime($subject),0,50);
               $message{subject} =~ s/\'//;
               };
            }
         # Set the score if we had one
         if( $score ne '-' )
            {
            #$score =~ tr/./,/;
            $message{spam_score} = $score;
            }
         # Set our action/reason codes
         $message{action_id} = $action;
         $message{reason_id} = $reason;
         # Pull in the reason_extra field. This has the extra info the filter that matched
         # and other things that might be multi-byte so it should probably be eval’d
         $message{msgsize} = $msgsize;
         eval
            {
               if( $reason_extra ne '-' )
               {
               $message{reason_extra} = unmime $reason_extra ;
               }
            }
         }
         else
         {
           print "Wrong SCAN Format!";
         }
         # Write to database
         $message{action_text} = $action{$message{action_id}};
         if ($message{action_id} >0) {
            $message{reason_text} = $reason{$message{reason_id}};
            } else {
            $message{reason_text} = "(Allowed)";
            }
         if ($message{from} & $message{mailto}) {
            $query = "INSERT INTO maillog (Timestamp,Sender,Recipient,Subject,Action,Reason,ReasonExt,Score,SourceIP,MsgID,Domain,Size,Cluster) VALUES ('$message{time}','$message{from}','$message{mailto}','$message{subject}','$message{action_text}','$message{reason_text}','$message{reason_extra}','$message{spam_score}','$message{client}','$message{id}','$message{recp_domain}','$message{msgsize}','$cluster')";
            $sth = $dbh->prepare($query);
            $sth->execute();
         }
      }
   elsif( $name =~ /SEND/ )
      {
         # Break the Outbound MTA info up into encrypted/action/queue_id/response
         if( $info =~ /([^\s]+)\s(\d+)\s([^\s]+)\s(.*)$/ )
            {
            my ($enc, $action, $queue_id, $reason) = ($1, $2, $3, $4);
            $message{id} = $id;
            # Do whatever you would like with the delivery transactions – just keep in
            # mind that a single message may have multiple outbound entries because of
            # being deferred by the downstream server.
            $message{delivery} = $send_action{$action};
            # mm(at)elabnet.de / disabled as we have no time in logentry to see wether entry is old
            #$query = "UPDATE maillog SET Delivery = '$message{delivery}' WHERE MsgID = '$message{id}' AND Delivery != 'Delivered'";
            #$sth = $dbh->prepare($query);
            #$sth->execute();
            }
      }
   # Put a ref to this message onto our array of messages so we can use it later
   push(@message_list, \%message);
   # Send back whatever info you would like to the caller here. In this case
   # we are sending back the end time as an example that could handle tracking
   # last seen message time or something similar
   $message{action_text} = $action{$message{action_id}};
   if ($message{action_id} >0) {
      $message{reason_text} = $reason{$message{reason_id}};
      } else {
      $message{reason_text} = "-"; }
   if ($message{from} & $message{mailto}) {
#      print "Message: $message{time} $message{from} To: $message{mailto} Subject: Action: $message{action_text} Reason: $message{reason_text} ($message{reason_extra}) Score: $message{spam_score} \n";
#      print "Message: $message{time} $message{from} To: $message{mailto} Subject: $message{subject} Action: $message{action_text} Reason: $message{reason_text} ($message{reason_extra}) Score: $message{spam_score} \n";
      #print "$message{time},$message{from},$message{mailto},$message{subject},$message{action_text},$message{reason_text},$message{reason_extra},$message{spam_score},$message{client},$message{id},$message{recp_domain},$message{msgsize}\n";
#      print $message{subject};
      }
   return( $end_time );
   }

# No message info to send back
return undef;
} 
